﻿using System;
using System.CodeDom;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using FirebirdSql.Data.FirebirdClient;
using Microsoft.CSharp;
using System.Web.UI.WebControls;


namespace MvcApplication1.Controllers
{
    public class ObjectRender
    {
        public int id { get; set; }
        public string color { get; set; }
        public string type { get; set; }
    }
    public class HomeController : Controller
    {
        string connectionString =
            "User=SYSDBA;" +
            "Password=masterkey;" +
            "Database=C:\\HuyTest.fdb;" +
            "DataSource=localhost;" +
            "Port=3050;" +
            "Dialect=3;" +
            "Charset=NONE;" +
            "Role=;" +
            "Connection lifetime=15;" +
            "Pooling=true;" +
            "MinPoolSize=0;" +
            "MaxPoolSize=50;" +
            "Packet Size=8192;" +
            "ServerType=0";

        public ActionResult Index()
        {
            return View(getAllCategory());
        }

        [HttpPost]
        public ActionResult Index(string color, string type)
        {
            excuteNonQuery("INSERT INTO category(color, type) values ('" + color + "', '" + type + "')");
            return View(getAllCategory());
        }

        [HttpPost]
        public JsonResult deleteCategory(int id)
        {
            List<Object> data = new List<Object>();
            JsonResult r = new JsonResult();
            r.Data = excuteNonQuery("DELETE FROM category WHERE id=" + id);
            return r;
        }

        [HttpPost]
        public JsonResult updateCategory(int id, string color, string type)
        {
            List<Object> data = new List<Object>();
            JsonResult r = new JsonResult();
            r.Data = excuteNonQuery("UPDATE category "
                                    + "SET color='" + color + "', type='" + type + "' "
                                    + "WHERE id='" + id + "';");
            return r;
        }

        private int excuteNonQuery(string command)
        {
            // Set the ServerType to 1 for connect to the embedded server
            int err = -1;
            FbConnection myConnection = new FbConnection(connectionString);

            try {
            
            myConnection.Open();

            FbTransaction myTransaction = myConnection.BeginTransaction();

            FbCommand myCommand = new FbCommand();

            myCommand.CommandText = command;
            myCommand.Connection = myConnection;
            myCommand.Transaction = myTransaction;

            // Execute Update
            err = myCommand.ExecuteNonQuery();

            // Commit changes
            myTransaction.Commit();

            // Free command resources in Firebird Server
            myCommand.Dispose();

            } catch (Exception ex) {

            } finally {
                
                // Close connection
                myConnection.Close();
            }
            
            return err;

        }

        private List<ObjectRender> getAllCategory()
        {
            FbConnection myConnection = new FbConnection(connectionString);
            
            try {
                myConnection.Open();

                FbTransaction myTransaction = myConnection.BeginTransaction();

                FbCommand myCommand = new FbCommand();

                myCommand.CommandText =
                "SELECT * FROM category";
                myCommand.Connection = myConnection;
                myCommand.Transaction = myTransaction;
                FbDataReader myreader = myCommand.ExecuteReader();

                List<ObjectRender> renders = new List<ObjectRender>();

                while (myreader.Read())
                {
                    ObjectRender temp = new ObjectRender();
                    temp.id = Convert.ToInt32(myreader.GetValue(0).ToString());
                    temp.color = myreader.GetValue(1).ToString();
                    temp.type = myreader.GetValue(2).ToString();
                    renders.Add(temp);
                }
                myreader.Close(); // we are done with the reader
                return renders;

            } catch (Exception ex) {

            } finally {
                myConnection.Close();
            }
            return null;
        }
    }
}
